﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using System.Data.SQLite;

namespace MediaData
{
    public class SQLiteTypedDBTestThing<T> where T : DataSet, new()
    {
        public T Data { get; private set; }
        string database;
        SQLiteConnection connection;
        Dictionary<string, SQLiteDataAdapter> dataAdapters = new Dictionary<string, SQLiteDataAdapter>();


        public SQLiteTypedDBTestThing(string database)
        {
            this.database = database;
            Data = new T();
        }

        public bool Connect()
        {
            bool createNew = !File.Exists(database);
            string query = "Data Source=" + database + ";Version=3;New=" + (createNew ? "True" : "False") + ";";
            connection = new SQLiteConnection(query);
            connection.Open();
            if (createNew)
            {
                CreateTables();
            }
            return true;
        }

        private void CreateTables()
        {
            using (SQLiteCommand command = connection.CreateCommand())
            {
                foreach (DataTable table in Data.Tables)
                {
                    command.CommandText = SimpleCreateTable(table);
                    command.ExecuteNonQuery();
                }
            }
        }

        public ConnectionState State
        {
            get { return connection != null ? connection.State : ConnectionState.Closed; }
        }

        public void CloseConnection()
        {
            lock (this)
            {
                DisposeAdapters();

                if (connection != null)
                {
                    
                    try
                    {
                        connection.Dispose();
                    }
                    catch (Exception)
                    {
                    }
                }
            }
        }

        private void DisposeAdapters()
        {
            foreach (SQLiteDataAdapter adapter in dataAdapters.Values)
            {
                adapter.Dispose();
            }
            dataAdapters.Clear();
        }


        void Fill(DataSet set, string table, SQLiteCommand command)
        {
            SQLiteDataReader reader = command.ExecuteReader();

            DataTable t = set.Tables[table];
            while (reader.Read())
            {
                object[] values = new object[reader.FieldCount];
                reader.GetValues(values);
                t.Rows.Add(values);
            }
            reader.Dispose();
            command.Dispose();

        }

        List<string> filledTables = new List<string>();

        public void LoadData()
        {
            Data = new T();
            DisposeAdapters();
            filledTables.Clear();

            foreach (DataTable table in Data.Tables)
            {
                FillTable(table);

            }

            Data.AcceptChanges();
        }

        public void FillTable(DataTable table)
        {
            if (filledTables.Contains(table.TableName))
                return;

            foreach (DataRelation parent in table.ParentRelations)
            {
                FillTable(parent.ParentTable);
            }
            
            if (filledTables.Contains(table.TableName))
                return;

            SQLiteDataAdapter adapter = new SQLiteDataAdapter("SELECT * FROM " + table.TableName, connection);
            dataAdapters.Add(table.TableName, adapter);
            SQLiteCommandBuilder mcb1 = new SQLiteCommandBuilder(adapter);

            Fill(Data, table.TableName, new SQLiteCommand("SELECT * FROM " + table.TableName, connection));
            filledTables.Add(table.TableName);
        }


        public string SimpleCreateTable(DataTable table)
        {
            List<string> keys = new List<string>();
            List<string> columns = new List<string>();

            foreach (DataColumn col in table.PrimaryKey)
            {
                keys.Add("`" + col.ColumnName+"`");
            }



            foreach (DataColumn col in table.Columns)
            {
                string dataType = "";

                if (col.DataType == typeof(Int32))
                {
                    dataType = "int";
                }
                else if (col.DataType == typeof(Int64))
                {
                    dataType = "bigint";
                }
                else if (col.DataType == typeof(string))
                {
                    dataType = "text";
                }
                
                
                columns.Add(string.Format("`{0}` {1} NOT NULL", col.ColumnName, dataType));
            }
            if(keys.Count > 0)
                columns.Add("PRIMARY KEY  ("+string.Join(",", keys.ToArray())+")");
            return "CREATE TABLE `" + table.TableName + "` (" + string.Join(",", columns.ToArray()) + ");";
        }
        
        public bool Commit(T changes)
        {
            if (changes == null)
                return false;
            using (SQLiteTransaction t = connection.BeginTransaction())
            {
                if (changes != null)
                {
                    foreach (var adapter in dataAdapters)
                    {
                        adapter.Value.Update(changes, adapter.Key);
                    }



                    t.Commit();
                    if (MediaChanged != null)
                        MediaChanged(changes);
                    return true;
                }
                else
                {
                    return false;
                }
            }
    
        }
        
        public event Action<DataSet> MediaChanged;

        ~SQLiteTypedDBTestThing()
        {
            CloseConnection();
        }

    }

}
